CREATE TABLE [dbo].[tb_PlanesEstudio]
(
[idPlanEstudio] [int] NOT NULL IDENTITY(1, 1),
[Descripcion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NormaCreacion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FechaAlta] [datetime] NOT NULL,
[Baja] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FechaBaja] [datetime] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[BajasLogicastb_PlanesEstudio] ON [dbo].[tb_PlanesEstudio]
FOR UPDATE AS
if update(baja) and (Select baja from inserted)='S' and (Select FechaBaja from inserted) is null BEGIN
	UPDATE tb_PlanesEstudio set FechaBaja = getdate() where idPlanEstudio=(Select idPlanEstudio from inserted)
END
else
if (update(baja) and (Select baja from inserted)='N')	UPDATE tb_PlanesEstudio set FechaBaja= null where idPlanEstudio=(Select idPlanEstudio from inserted)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[deletetb_PlanesEstudio] ON [dbo].[tb_PlanesEstudio]
 FOR DELETE AS 
		DELETE tb_Carreras FROM tb_Carreras
		 WHERE Planid = (SELECT idPlanEstudio FROM deleted) 
		DELETE tb_RelPlanesSuborg FROM tb_RelPlanesSuborg
		 WHERE idPlanEstudio = (SELECT idPlanEstudio FROM deleted)
		 

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[updatetb_PlanesEstudio] ON [dbo].[tb_PlanesEstudio]
 FOR UPDATE AS 
 IF (update(idPlanEstudio)) begin 
	 SELECT * FROM inserted 
	 IF @@ROWCOUNT = 1 begin 
		 UPDATE tb_RelPlanesSuborg
		 SET idPlanEstudio = (SELECT idPlanEstudio FROM inserted)
		 WHERE tb_RelPlanesSuborg.idPlanEstudio = (SELECT idPlanEstudio FROM deleted)
		 UPDATE tb_Carreras
		 SET Planid = (SELECT idPlanEstudio FROM inserted)
		 WHERE tb_Carreras.Planid = (SELECT idPlanEstudio FROM deleted)
	end  ELSE begin 
		 ROLLBACK TRANSACTION 
 end 
 end 
GO
ALTER TABLE [dbo].[tb_PlanesEstudio] ADD CONSTRAINT [PK_tb_PlanesEstudio] PRIMARY KEY NONCLUSTERED  ([idPlanEstudio]) ON [PRIMARY]
GO
EXEC sp_bindrule N'[dbo].[S-N]', N'[dbo].[tb_PlanesEstudio].[Baja]'
GO
